#!/usr/local/bin/python
# -*- coding: utf-8 -*-
import datetime

import xlrd2 as xlrd
import xlwt
import initAlipayPage
from initLogger import logger


class BillAssistant:
    '''
    类功能：用于账单表格的预处理工作，目前主要处理支付宝账单
    核心功能为，将两张不同维度的支付宝账单合并成一张，
    '''




    def __init__(self, year=2022, month=1):
        '''
        函数功能：
        '''
        self.firstPrefix="first_"
        self.otherPrefix="other_"
        self.firstHeaderList = [] #第一个表的表头
        self.otherHeaderList = []  #第二个表的表头
        self.tableHeaderList=[] #整体表头
        self.combineDate={}  #合并后的数据

    def importXlsxFile(self, filePath):
        '''
        函数功能：导入一个xlsx 的excel文件
        :param filePath:
        :return:  返回一个数组，每个数组元素包含excel数据中的一行
        '''
        pass

        workbook = xlrd.open_workbook(filePath)
        table = workbook.sheet_by_index(0)
        return table


    def exportXlsxFile(self, filePath):
        '''
        函数功能
         :param filePath:
         :param fileData:
         :return:
        '''

        workbook = xlwt.Workbook(encoding="utf-8")
        workSheet = workbook.add_sheet('Sheet1')

        datetimeStyle = xlwt.XFStyle()
        datetimeStyle.num_format_str = 'YYYY/MM/DD hh:mm:ss'

        #for firstTitle in tableHeader:
        for i in range(0, len(self.tableHeaderList)):
            workSheet.write(0, i, self.tableHeaderList[i])


        i=1
        j=0
        #for i in range(0, len(self.self.combineDate)):
        for key in self.combineDate:

            curRowData=self.combineDate[key]
            print(curRowData)
            #for tinyKey in curRowData:
            for j in range(0, len(self.tableHeaderList)):
                #curCellData = curRowData[self.tableHeaderList[j]]
                subKey= self.tableHeaderList[j]
                curCellData = curRowData.get(subKey, "")

                if (type(curCellData)==datetime.datetime):

                    workSheet.write(i, j, curCellData, datetimeStyle)
                else:
                    workSheet.write(i, j, curCellData)

            #end for j in range(0, len(self.tableHeaderList)):
            i=i+1


        workbook.save(filePath)
        pass


    def loadfirstXlsxData(self, filePath, combineKey):
        '''
        函数功能：加载第一个xlsx文件数据
        :param filePath:
        :param combineKey:
        :return:
        '''


        logger.debug("call function loadfirstXlsxData start ")

        workbook = xlrd.open_workbook(filePath)
        dataTable = workbook.sheet_by_index(0)

        #读取表头
        index=0
        for index in range(dataTable.nrows):
            rowList = dataTable.row_values(rowx=index, start_colx=0, end_colx=None)
            print(rowList)
            if len(rowList) < initAlipayPage.ALIPAY_BILL_COLUMN_COUNT - 1:
                logger.debug("账单数据异常：%s" % str(rowList))
                continue


            if len(self.firstHeaderList)==0:
                for headerItem in rowList:
                    self.firstHeaderList.append(self.firstPrefix + headerItem.strip())
                    #self.firstDataKeyList = rowList
                break #结束for循环
        #加载核心数据
        rowNum = dataTable.nrows
        colNum = dataTable.ncols

        for i in range(index+1, rowNum):  #读取一行数据
            dataRowItemDic = {}
            for j in range(0, colNum):
                key = self.firstHeaderList[j]
                cellDataType= dataTable.cell(i, j).ctype
                if cellDataType==1: # 字符串类型
                    dataRowItemDic[key] = dataTable.cell(i, j).value.strip()
                elif cellDataType==3: # date类型
                    dataRowItemDic[key] = xlrd.xldate_as_datetime(dataTable.cell(i, j).value, workbook.datemode)
                else:
                    dataRowItemDic[key] = dataTable.cell(i, j).value
            #end for j in range(0, colNum):
            combineDateKey = dataRowItemDic[self.firstPrefix + combineKey]
            self.combineDate[combineDateKey] = dataRowItemDic

        #end for i in range(index + 1, rowNum):
        self.tableHeaderList = self.firstHeaderList

        logger.debug("call function loadfirstXlsxData end ")

    def combineOtherXlsxData(self, filePath, combineKey):
        '''
        函数功能：合并其他文件
        :param FilePath:
        :param CombineKey:
        :return:
        '''

        logger.debug("call function combineOtherXlsxData start ")

        workbook = xlrd.open_workbook(filePath)
        dataTable = workbook.sheet_by_index(0)

        # 读取表头
        index=0
        for index in range(dataTable.nrows):
            rowList = dataTable.row_values(rowx=index, start_colx=0, end_colx=None)
            print(rowList)
            if len(rowList) < initAlipayPage.NEW_ALIPAY_BILL_COLUMN_COUNT_OTHER - 1:
                logger.debug("账单数据异常：%s" % str(rowList))
                continue


            if len(self.otherHeaderList)==0:
                for headerItem in rowList:
                    self.otherHeaderList.append(self.otherPrefix + headerItem.strip())
                    #self.firstDataKeyList = rowList
                break #结束for循环

        #end for

        #加载核心数据
        rowNum = dataTable.nrows
        colNum = dataTable.ncols
        for i in range(index+1, rowNum):  #读取一行数据
            dataRowItemDic = {}
            rowList = dataTable.row_values(rowx=i, start_colx=0, end_colx=None)
            print(rowList)

            if (len(rowList) < initAlipayPage.NEW_ALIPAY_BILL_COLUMN_COUNT_OTHER - 1):
                logger.debug("账单数据异常：%s" % str(rowList))
                continue

            for j in range(0, colNum):
                key = self.otherHeaderList[j]
                cellDataType= dataTable.cell(i, j).ctype
                if cellDataType==1: # 字符串类型
                    dataRowItemDic[key] = dataTable.cell(i, j).value.strip()
                elif cellDataType==3: # date类型
                    dataRowItemDic[key] = xlrd.xldate_as_datetime(dataTable.cell(i, j).value, workbook.datemode)
                else:
                    dataRowItemDic[key] = dataTable.cell(i, j).value
            #end for j in range(0, colNum):
            combineDateKey = dataRowItemDic[self.otherPrefix + combineKey]

            if self.combineDate.get(combineDateKey, None) == None:
                logger.warning("key %s  abnormal  "%combineDateKey)
                #self.combineDate[combineDateKey] = dataRowItemDic
            else:
                self.combineDate[combineDateKey].update(dataRowItemDic)
        # end for i in range(index+1, rowNum):

        self.tableHeaderList = self.tableHeaderList + self.otherHeaderList
        self.otherHeaderList=[]
        logger.debug("call function combineOtherXlsxData end ")




    def combineXlsxData(self, fistFilePath, firstCombineKey,secondFilePath, secondCombineKey):
        '''
        函数功能：将两个excel文件以某一个关键词字段进行关联
        :param fistFilePath:
        :param secondFilePath:
        :param combineKey:
        :return:
        '''

        logger.debug("call function combineXlsxData ")

        combineDate = {}
        firstDataTable = self.importXlsxFile(fistFilePath)




        #处理第一类数据
        self.firstHeaderList = []
        for index in range(firstDataTable.nrows):
            rowList = firstDataTable.row_values(rowx=index, start_colx=0, end_colx=None)
            print(rowList)
            if len(rowList) < initAlipayPage.ALIPAY_BILL_COLUMN_COUNT - 1:
                logger.debug("账单数据异常：%s" % str(rowList))
                continue


            if len(self.firstHeaderList)==0:
                for headerItem in rowList:
                    self.firstHeaderList.append("first_" + headerItem.strip())
                    #self.firstDataKeyList = rowList
            else:
                firstDataItemDic= {}
                for rowListIndex in range(len(rowList)):
                    key = self.firstHeaderList[rowListIndex]
                    #firstDataItemDic["first_"+key]=rowList[rowListIndex].strip()
                    firstDataItemDic[key] = rowList[rowListIndex].strip()

                combineDateKey = firstDataItemDic["first_"+firstCombineKey]
                combineDate[combineDateKey] = firstDataItemDic

        # 处理第二类数据
        logger.debug("second table ")
        secondDataTable = self.importXlsxFile(secondFilePath)
        self.secondDataKeyList = []
        for index in range(secondDataTable.nrows):
            rowList = secondDataTable.row_values(rowx=index, start_colx=0, end_colx=None)
            print(rowList)
            if len(rowList) < initAlipayPage.NEW_ALIPAY_BILL_COLUMN_COUNT_OTHER - 1:
                logger.debug("账单数据异常：%s" % str(rowList))
                continue

            if len(self.secondDataKeyList) == 0:
                #self.secondDataKeyList = rowList
                for headerItem in rowList:
                    self.secondDataKeyList.append("secon_" + headerItem.strip())


            else:
                secondDataItemDic= {}
                for rowListIndex in range(len(rowList)):
                    key = self.secondDataKeyList[rowListIndex]
                    #secondDataItemDic["secon_"+key]=rowList[rowListIndex]
                    secondDataItemDic[key] = rowList[rowListIndex].strip()

                combineDateKey = secondDataItemDic["secon_"+secondCombineKey]
                if combineDate.get(combineDateKey, None)==None:
                    combineDate[combineDateKey] = secondDataItemDic
                else:
                    combineDate[combineDateKey].update(secondDataItemDic)



        logger.debug("call function combineXlsxData end ")



def main():
    #fistFilePath="I:\\anbc_work\\code\\AccountBoy\\doc\\月度账单\\test_alipay_bill_2024_1_A.xlsx"
    #fistFilePath = r"I:\anbc_work\code\AccountBoy\doc\月度账单\test_2024年\alipay_A_2025_1.xlsx"
    #fistFilePath = r"I:\anbc_work\code\AccountBoy\doc\月度账单\test_2024年\alipay_A_2024_3-new.xlsx"
    #otherFilePath = r"I:\anbc_work\code\AccountBoy\doc\月度账单\test_2024年\alipay_B_2025_1.xlsx"
    #exportFilePath = r"I:\anbc_work\code\AccountBoy\doc\月度账单\test_2024年\alipay_C_2025_1.xlsx"

    #fistFilePath = r"I:\anbc_work\code\AccountBoy\doc\月度账单\2025年\alipay_A_2025_6.xlsx"
    #otherFilePath = r"I:\anbc_work\code\AccountBoy\doc\月度账单\2025年\alipay_B_2025_6.xlsx"
    #exportFilePath = r"I:\anbc_work\code\AccountBoy\doc\月度账单\2025年\alipay_C_2025_6.xlsx"

    fistFilePath = r"I:\软件run\小狗记账_3.0\月度账单\2025年\alipay_A_2025_10.xlsx"
    otherFilePath = r"I:\软件run\小狗记账_3.0\月度账单\2025年\alipay_B_2025_10.xlsx"
    exportFilePath = r"I:\软件run\小狗记账_3.0\月度账单\2025年\alipay_C_2025_10.xlsx"

    billAssistant = BillAssistant()
    billAssistant.loadfirstXlsxData(fistFilePath, "交易订单号")
    billAssistant.combineOtherXlsxData(otherFilePath, "交易号")
    #billAssistant.combineXlsxData(fistFilePath, "交易订单号", secondFilePath, "交易号                  ")

    billAssistant.exportXlsxFile(exportFilePath) #test.xlsx"

if __name__== "__main__":
    main()